﻿using System;
using System.Collections.Generic;
using System.Text;
using Common;
using System.Data;
using System.Data.SqlClient;



namespace DA
{
    public class DBMenus : IDisposable
    {
        string sql;
        SqlHelpers sqlh;
        DataSet ds;
        SqlDataReader dr;
        public DBMenus()
        {
            sqlh = new SqlHelpers();

        }
        public DBMenus(SqlHelpers sh)
        {
            sqlh = sh;
        }
        public void Dispose()
        {
            sqlh.Dispose();
        }


        /// <summary>
        /// DB--查询所有菜品的方法
        /// </summary>
        /// <returns></returns>
        public List<CMenus> DBMenusQuery()
        {
            List<CMenus> lm = new List<CMenus>();
            sql = "select * from Menus";
            dr = sqlh.RQuery(sql, null, CommandType.Text);
            while (dr.Read())
            {
                CMenus cm = new CMenus();
                cm.CmsId1 = Convert.ToInt32(dr["msId"]);
                cm.CmtId1 = Convert.ToInt32(dr["mtId"]);
                cm.CmsName1 = dr["msName"].ToString();
                cm.CmsSpell1 = dr["msSpell"].ToString();
                cm.CmsUnit1 = dr["msUnit"].ToString();
                cm.CmsPrice1 = Convert.ToSingle(dr["msPrice"]);
                cm.CmsScalar1 = Convert.ToInt32(dr["msScalar"]);
                cm.CmsCost1 = Convert.ToSingle(dr["msCost"]);
                cm.CmsMoney1 = Convert.ToSingle(dr["msMoney"]);
                cm.CstId1 = Convert.ToInt32(dr["stId"]);
                lm.Add(cm);
            }
            dr.Close();
            return lm;

        }

        /// <summary>
        /// DB  根据拼音缩写查询菜品的方法
        /// </summary>
        /// <param name="spell">拼音缩写</param>
        /// <returns></returns>
        public List<CMenus> DBMenusQueryMsSpell(string spell)
        {
            List<CMenus> lm = new List<CMenus>();
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@spell", SqlDbType.VarChar, 20));
            pars[0].Value = spell;
            sql = "select * from Menus where msSpell like '%'+@spell+'%'";
            dr = sqlh.RQuery(sql, pars, CommandType.Text);
            while (dr.Read())
            {
                CMenus cm = new CMenus();
                cm.CmsId1 = Convert.ToInt32(dr["msId"]);
                cm.CmtId1 = Convert.ToInt32(dr["mtId"]);
                cm.CmsName1 = dr["msName"].ToString();
                cm.CmsSpell1 = dr["msSpell"].ToString();
                cm.CmsUnit1 = dr["msUnit"].ToString();
                cm.CmsPrice1 = Convert.ToSingle(dr["msPrice"]);
                cm.CmsScalar1 = Convert.ToInt32(dr["msScalar"]);
                cm.CmsCost1 = Convert.ToSingle(dr["msCost"]);
                cm.CmsMoney1 = Convert.ToSingle(dr["msMoney"]);
                cm.CstId1 = Convert.ToInt32(dr["stId"]);
                lm.Add(cm);
            }
            dr.Close();
            return lm;
        }

        /// <summary>
        /// 查询根据拼音缩写得到菜品的总额的方法
        /// </summary>
        /// <param name="spell">拼音缩写</param>
        ///// <returns></returns>
        //public float DBMenusQueryMsSpellMoney(string spell)
        //{
        //    List<SqlParameter> pars = new List<SqlParameter>();
        //    pars.Add(new SqlParameter("@spell", SqlDbType.VarChar, 20));
        //    pars[0].Value = spell;
        //    sql = "select sum(msMoney) from Menus where msSpell like @spell+'%'";
        //    return sqlh.ExcuteInsert (sql, pars);
        //}

        /// <summary>
        /// 根据菜品名称查询菜品编号的 方法
        /// </summary>
        /// <param name="typeid">查询类型(0为菜品编号,1为菜品单价)</param>
        /// <param name="msName">菜品名称</param>
        /// <returns></returns>
        public int DBMenusNameQueryID(int typeid, string msName)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@msName", SqlDbType.VarChar, 20));
            pars[0].Value = msName;
            if (typeid == 0)//0为菜品编号
            {
                sql = "select msid from Menus where msName=@msName";
            }
            if (typeid == 1)//1为菜品单价
            {
                sql = "select msPrice from Menus where msName=@msName";
            }
            return sqlh.ExcuteInsert(sql, pars);

        }

        /// <summary>
        /// 根据仓库类型查询商品的方法
        /// </summary>
        /// <param name="?">仓库类型编号</param>
        /// <returns></returns>
        public List<CMenus> DBMenusStoreType(int stid)
        {
            List<CMenus> lm = new List<CMenus>();
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@stId", SqlDbType.Int));
            pars[0].Value = stid;
            sql = "select * from Menus where stId=@stId";
            dr = sqlh.RQuery(sql, pars, CommandType.Text);
            while (dr.Read())
            {
                CMenus cm = new CMenus();
                cm.CmsId1 = Convert.ToInt32(dr["msId"]);
                cm.CmtId1 = Convert.ToInt32(dr["mtId"]);
                cm.CmsName1 = dr["msName"].ToString();
                cm.CmsSpell1 = dr["msSpell"].ToString();
                cm.CmsUnit1 = dr["msUnit"].ToString();
                cm.CmsPrice1 = Convert.ToSingle(dr["msPrice"]);
                cm.CmsScalar1 = Convert.ToInt32(dr["msScalar"]);
                cm.CmsCost1 = Convert.ToSingle(dr["msCost"]);
                cm.CmsMoney1 = Convert.ToSingle(dr["msMoney"]);
                cm.CstId1 = Convert.ToInt32(dr["stId"]);
                lm.Add(cm);
            }
            dr.Close();
            return lm;
        }

        /// <summary>
        /// 根据商品类型查询商品的方法
        /// </summary>
        /// <param name="mtId">商品类型编号</param>
        /// <returns></returns>
        public List<CMenus> DBMenusTypeQueryID(int mtId)
        {
            List<CMenus> lm = new List<CMenus>();
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@mtId", SqlDbType.Int));
            pars[0].Value = mtId;
            sql = "select * from Menus where mtId=@mtId";
            dr = sqlh.RQuery(sql, pars, CommandType.Text);
            while (dr.Read())
            {
                CMenus cm = new CMenus();
                cm.CmsId1 = Convert.ToInt32(dr["msId"]);
                cm.CmtId1 = Convert.ToInt32(dr["mtId"]);
                cm.CmsName1 = dr["msName"].ToString();
                cm.CmsSpell1 = dr["msSpell"].ToString();
                cm.CmsUnit1 = dr["msUnit"].ToString();
                cm.CmsPrice1 = Convert.ToSingle(dr["msPrice"]);
                cm.CmsScalar1 = Convert.ToInt32(dr["msScalar"]);
                cm.CmsCost1 = Convert.ToSingle(dr["msCost"]);
                cm.CmsMoney1 = Convert.ToSingle(dr["msMoney"]);
                cm.CstId1 = Convert.ToInt32(dr["stId"]);
                lm.Add(cm);
            }
            dr.Close();
            return lm;
        }
        /// <summary>
        /// 根据商品编号查询商品的方法
        /// </summary>
        /// <param name="mtId">商品编号</param>
        /// <returns></returns>
        public List<CMenus> DBMenusIDQueryID(int msId)
        {
            List<CMenus> lm = new List<CMenus>();
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@msId", SqlDbType.Int));
            pars[0].Value = msId;
            sql = "select * from Menus where msId=@msId";
            dr = sqlh.RQuery(sql, pars, CommandType.Text);
            while (dr.Read())
            {
                CMenus cm = new CMenus();
                cm.CmsId1 = Convert.ToInt32(dr["msId"]);
                cm.CmtId1 = Convert.ToInt32(dr["mtId"]);
                cm.CmsName1 = dr["msName"].ToString();
                cm.CmsSpell1 = dr["msSpell"].ToString();
                cm.CmsUnit1 = dr["msUnit"].ToString();
                cm.CmsPrice1 = Convert.ToSingle(dr["msPrice"]);
                cm.CmsScalar1 = Convert.ToInt32(dr["msScalar"]);
                cm.CmsCost1 = Convert.ToSingle(dr["msCost"]);
                cm.CmsMoney1 = Convert.ToSingle(dr["msMoney"]);
                lm.Add(cm);
            }
            dr.Close();
            return lm;
        }
        /// <summary>
        /// 根据商品编号删除商品的方法
        /// </summary>
        /// <param name="msId">商品编号</param>
        public void DBMenusDelete(int msId)
        {

            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@msId", SqlDbType.Int));
            pars[0].Value = msId;
            sql = "delete from Menus where msId=@msId";
            sqlh.NonQuery(sql, pars, CommandType.Text);
        }

        /// <summary>
        /// -
        /// </summary>
        /// <returns></returns>
        public DataSet DBMenuAndStore()
        {
            sql = "select ms.msId,ms.msName,st.stName from Menus ms inner join StorehouseType st on ms.stId=st.stId";
            ds = sqlh.ExcuteSelect(sql, "MST", null);
            return ds;
        }
        /// <summary>
        /// +
        /// </summary>
        /// <param name="spell"></param>
        /// <returns></returns>
        public DataSet DBMenuAndStoreBySpell(string spell)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@spell", SqlDbType.VarChar, 20));
            pars[0].Value = spell;
            sql = "select ms.msId,ms.msName,st.stName from Menus ms inner join StorehouseType st on ms.stId=st.stId where ms.msSpell like @spell+'%'";
            ds = sqlh.ExcuteSelect(sql, "MST", pars);
            return ds;
        }
        /// <summary>
        /// 商品进货修改库存的方法
        /// </summary>
        /// <param name="cm"></param>
        public void DBMenusJXGStock(CMenus cm)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@msId", SqlDbType.Int));
            pars[0].Value = cm.CmsId1;
            pars.Add(new SqlParameter("@msScalar", SqlDbType.Int));
            pars[1].Value = cm.CmsScalar1;
            pars.Add(new SqlParameter("@msCost", SqlDbType.Float));
            pars[2].Value = cm.CmsCost1;
            pars.Add(new SqlParameter("@msMoney", SqlDbType.Float));
            pars[3].Value = cm.CmsMoney1;
            //pars.Add(new SqlParameter("@stId", SqlDbType.Int));
            //pars[4].Value = cm.CstId1 ;
            if (Convert.ToInt32(pars[2].Value) == 0)
            {
                sql = "update Menus set msScalar=msScalar+@msScalar,msCost=@msCost,msMoney=msMoney+@msMoney where msId=@msId";
            }
            else
            {
                sql = "update Menus set msScalar=msScalar+@msScalar,msCost=(msCost+@msCost)/2,msMoney=msMoney+@msMoney where msId=@msId";
            }
            sqlh.NonQuery(sql, pars, CommandType.Text);


        }
        /// <summary>
        /// 销售时修改库存的方法
        /// </summary>
        /// <param name="cm"></param>
        public void DBMenusUpdataStock(CMenus cm)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@msIdd", SqlDbType.Int));
            pars[0].Value = cm.CmsId1;
            pars.Add(new SqlParameter("@msScalarr", SqlDbType.Int));
            pars[1].Value = cm.CmsScalar1;
            pars.Add(new SqlParameter("@msMoneyy", SqlDbType.Float));
            pars[2].Value = cm.CmsMoney1;
            //if (type == 1)
            //{
            sql = "update Menus set msScalar=msScalar-@msScalarr,msMoney=msMoney-@msMoneyy where msId=@msIdd";
            //}
            //if (type == 2)
            //{
            //    sql = "update Menus set msScalar=msScalar+@msScalar,msMoney=msMoney+@msMoney where msId=@msId";
            //}
            sqlh.NonQuery(sql, pars, CommandType.Text);
        }
        /// <summary>
        /// 删除时修改数量单价和总额的方法
        /// </summary>
        /// <param name="m"></param>
        public void DBMenusDeleteUpdataMoney(CMenus cm)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@msId", SqlDbType.Int));
            pars[0].Value = cm.CmsId1;
            pars.Add(new SqlParameter("@msScalar", SqlDbType.Int));
            pars[1].Value = cm.CmsScalar1;
            pars.Add(new SqlParameter("@msCost", SqlDbType.Float));
            pars[2].Value = cm.CmsCost1;
            pars.Add(new SqlParameter("@msMoney", SqlDbType.Float));
            pars[3].Value = cm.CmsMoney1;
            sql = "update Menus set msScalar=msScalar-@msScalar,msCost=msCost*2-@msCost,msMoney=msMoney-@msMoney where msId=@msId";
            sqlh.NonQuery(sql, pars, CommandType.Text);
        }
        /// <summary>
        /// 插入商品信息的方法
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public int DBMenusInsert(CMenus m)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@mtId", SqlDbType.Int));
            pars[0].Value = m.CmtId1;
            pars.Add(new SqlParameter("@msName", SqlDbType.VarChar, 20));
            pars[1].Value = m.CmsName1;
            pars.Add(new SqlParameter("@msSpell", SqlDbType.VarChar, 20));
            pars[2].Value = m.CmsSpell1;
            pars.Add(new SqlParameter("@msUnit", SqlDbType.VarChar, 10));
            pars[3].Value = m.CmsUnit1;
            pars.Add(new SqlParameter("@msPrice", SqlDbType.Float));
            pars[4].Value = m.CmsPrice1;
            pars.Add(new SqlParameter("@msScalar", SqlDbType.Int));
            pars[5].Value = m.CmsScalar1;
            pars.Add(new SqlParameter("@msCost", SqlDbType.Float));
            pars[6].Value = m.CmsCost1;
            pars.Add(new SqlParameter("@msMoney", SqlDbType.Float));
            pars[7].Value = m.CmsMoney1;
            pars.Add(new SqlParameter("@stId", SqlDbType.Int));
            pars[8].Value = m.CstId1;
            sql = "insert into Menus values(@mtId,@msName,@msSpell,@msUnit,@msPrice,@msScalar,@msCost,@msMoney,@stId) select @@identity";
            return sqlh.ExcuteInsert(sql, pars);
        }
        /// <summary>
        /// 修改商品信息的方法
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public int DBMenusUpdate(CMenus m)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@msId", SqlDbType.Int));
            pars[0].Value = m.CmsId1;
            pars.Add(new SqlParameter("@mtId", SqlDbType.Int));
            pars[1].Value = m.CmtId1;
            pars.Add(new SqlParameter("@msName", SqlDbType.VarChar, 20));
            pars[2].Value = m.CmsName1;
            pars.Add(new SqlParameter("@msSpell", SqlDbType.VarChar, 20));
            pars[3].Value = m.CmsSpell1;
            pars.Add(new SqlParameter("@msUnit", SqlDbType.VarChar, 10));
            pars[4].Value = m.CmsUnit1;
            pars.Add(new SqlParameter("@msPrice", SqlDbType.Float));
            pars[5].Value = m.CmsPrice1;
            pars.Add(new SqlParameter("@msCost", SqlDbType.Float));
            pars[6].Value = m.CmsCost1;
            pars.Add(new SqlParameter("@msMoney", SqlDbType.Float));
            pars[7].Value = m.CmsMoney1;
            sql = "update Menus set mtId=@mtId,msName=@msName,msSpell=@msSpell,msUnit=@msUnit,msPrice=@msPrice,msCost=@msCost,msMoney=@msMoney where msId=@msId";
            sqlh.NonQuery(sql, pars, CommandType.Text);
            return 1;
            //  return sqlh.ExcuteInsert(sql, pars);
        }

        /// <summary>
        /// DB 修改商品存储仓库的方法
        /// </summary>
        /// <param name="msId"></param>
        /// <param name="stId"></param>
        public void DBMenusUpdateCK(int msId, int stId)
        {
            List<SqlParameter> pars = new List<SqlParameter>();
            pars.Add(new SqlParameter("@msId", SqlDbType.Int));
            pars[0].Value = msId;
            pars.Add(new SqlParameter("@stId", SqlDbType.Int));
            pars[1].Value = stId;
            sql = "update Menus set stId=@stId where msId=@msId";
            sqlh.ExcuteInsertUpdateDelete(sql, pars);
        }
        /// <summary>
        /// 查询供应商
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public DataSet QueryGys(string name)
        {
            return SqlHelper.ExecuteDataset(SqlHelper.strconn, "pro_QueryGys", name);
        }
        /// <summary>
        /// 删除供应商
        /// </summary>
        /// <param name="fsid"></param>
        public void DelGys(int fsid)
        {
            SqlHelper.ExecuteNonQuery(SqlHelper.strconn, "DelGys", fsid);
        }
        /// <summary>
        /// 插入新供应商
        /// </summary>
        /// <param name="fsName"></param>
        /// <param name="fsLinkman"></param>
        /// <param name="fsLinkphone"></param>
        /// <param name="fsAddress"></param>
        /// <param name="fsRemark"></param>
        public void InsertNewGys(string fsName, string fsLinkman, string fsLinkphone, string fsAddress, string fsRemark)
        {
            SqlHelper.ExecuteNonQuery(SqlHelper.strconn, "pro_InsertNewGys", fsName, fsLinkman, fsLinkphone, fsAddress, fsRemark);
        }
    }
}
